package com.yenlien.traceyun.business.service;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.yenlien.traceyun.business.dao.MaterialManagementDao;
import com.yenlien.traceyun.business.entity.MaterialManagement;
import com.yenlien.traceyun.webase.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.criteria.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 物料管理
 * 2020-12-24
 * PurpleStar
 */
@Service
public class MaterialManagementService {

    @Autowired
    private MaterialManagementDao materialManagementDao;


    public Page<MaterialManagement> findByList(final MaterialManagement materialManagement) {
        Pageable pageable = PageRequest.of(materialManagement.getPage(), materialManagement.getPageSize());
        return materialManagementDao.findAll((Root<MaterialManagement> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            return genPredicate(root, query, cb, materialManagement);
        }, pageable);
    }

    public List<MaterialManagement> findByListNoPage(final MaterialManagement materialManagement) {
        return materialManagementDao.findAll((Root<MaterialManagement> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            return genPredicate(root, query, cb, materialManagement);
        });
    }


    public Predicate genPredicate(Root<MaterialManagement> root, CriteriaQuery<?> query, CriteriaBuilder cb, MaterialManagement materialManagement) {
        Path<String> materialType = root.get("materialType");
        Path<String> materialName = root.get("materialName");
        Path<String> materialCode = root.get("materialCode");
        //构造查询条件
        List<Predicate> list = new ArrayList<Predicate>();
        list.add(cb.equal(root.get("companyId"), materialManagement.getCompanyId()));
        if (StringUtils.isNotBlank(materialManagement.getMaterialType())) {
            list.add(cb.equal(materialType, materialManagement.getMaterialType()));
        }
        if (StringUtils.isNotBlank(materialManagement.getMaterialName())) {
            list.add(cb.equal(materialName, materialManagement.getMaterialName()));
        }
        if (StringUtils.isNotBlank(materialManagement.getMaterialCode())) {
            list.add(cb.equal(materialCode, materialManagement.getMaterialCode()));
        }
        Predicate[] predicate = new Predicate[list.size()];
        return cb.and(list.toArray(predicate));
    }


    /**
     * 添加物料管理
     *
     * @param management
     */
    @Transactional
    public void save(MaterialManagement management) {
        if (management.getMaterialType() != null && management.getMaterialType().length() > 0) {
            materialManagementDao.save(management);
        } else {
            throw new BusinessException("500", "添加失败，请重新添加");
        }
    }

    /**
     * 查询物料管理
     *
     * @param page
     * @return
     */
    public Page<MaterialManagement> select(Integer page) {
        Sort ageSort = Sort.by(Sort.Direction.DESC, "createtime");
        PageRequest pageRequest = PageRequest.of(page, 10, ageSort);
        Page<MaterialManagement> all = materialManagementDao.findAll(pageRequest);
        return all;
    }

    /**
     * 根据物料名称查询物料
     *
     * @param materialName
     * @return
     */
    public Object searchName(String materialName) {
        List<MaterialManagement> byMaterialName = materialManagementDao.findByMaterialName(materialName);
        ObjectMapper mapper = new ObjectMapper();
        String json = null;
        try {
            json = mapper.writeValueAsString(byMaterialName);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return json;
    }

    /**
     * 根据类型和名称查询物料
     *
     * @param materialType
     * @param materialName
     * @return
     */
    public Object search(String materialType, String materialName) {
        List<MaterialManagement> byMaterialTypeAndMaterialName = materialManagementDao.findByMaterialTypeAndMaterialName(materialType, materialName);
        ObjectMapper mapper = new ObjectMapper();
        String json = null;
        try {
            json = mapper.writeValueAsString(byMaterialTypeAndMaterialName);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return json;
    }

    /**
     * 根据类型查询物料
     *
     * @param materialType
     * @return
     */
    public Object searchType(String materialType) {
        List<MaterialManagement> byMaterialType = materialManagementDao.findByMaterialType(materialType);
        ObjectMapper mapper = new ObjectMapper();
        String json = null;
        try {
            json = mapper.writeValueAsString(byMaterialType);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return json;
    }

    /**
     * 根据id修改物料管理
     *
     * @param materialManagement
     */
    @Transactional
    public void update(MaterialManagement materialManagement) {
        materialManagementDao.save(materialManagement);
    }

    /**
     * 根据id删除物料管理
     *
     * @param id
     */
    @Transactional
    public void delete(String id) {
        materialManagementDao.deleteById(id);
    }

    /**
     * 导入excel物料管理
     *
     * @param file
     * @param userId
     * @param companyId
     */
    public void imports(MultipartFile file, String userId, String companyId) {
        //获取文件上传名字
        String originalFilename = file.getOriginalFilename();
        //获取文件流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<MaterialManagement> list = new ArrayList<>();

        // 判断文件是否是Excel(2003、2007)
        String suffix = originalFilename
                .substring(originalFilename.lastIndexOf("."), originalFilename.length());
        // Excel2003
        if (".xls".equals(suffix)) {
            POIFSFileSystem fileSystem = null;
            // 工作簿
            HSSFWorkbook workBook = null;
            try {
                fileSystem = new POIFSFileSystem(inputStream);
                workBook = new HSSFWorkbook(fileSystem);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            HSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            DecimalFormat df = new DecimalFormat("#");
            for (int i = 1; i <= rows; i++) {
                MaterialManagement management = new MaterialManagement();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容
                    String materialType = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialCode = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialSpecification = String.valueOf(sheet.getRow(i).getCell(j++).getNumericCellValue());
                    String unit = sheet.getRow(i).getCell(j++).getStringCellValue();
                    management.setMaterialType(materialType);
                    management.setMaterialName(materialName);
                    management.setMaterialCode(materialCode);
                    management.setMaterialSpecification(materialSpecification);
                    management.setUnit(unit);
                    management.setUserId(userId);
                    management.setCompanyId(companyId);
                    management.setCreatetime(new Date());
                }
                list.add(management);
            }
            // Excel2007
        } else if (".xlsx".equals(suffix)) {
            XSSFWorkbook workBook = null;
            try {
                workBook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 获取第一个工作簿
            XSSFSheet sheet = workBook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            //获取列数
            int clos = sheet.getRow(0).getPhysicalNumberOfCells();
            for (int i = 1; i <= rows; i++) {
                MaterialManagement management = new MaterialManagement();
                for (int j = 0; j <= clos; j++) {//获取单元格中内容
                    String materialType = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialName = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialCode = sheet.getRow(i).getCell(j++).getStringCellValue();
                    String materialSpecification = sheet.getRow(i).getCell(j++).getStringCellValue();
                    management.setMaterialType(materialType);
                    management.setMaterialName(materialName);
                    management.setMaterialCode(materialCode);
                    management.setMaterialSpecification(materialSpecification);
                    management.setUserId(userId);
                    management.setCompanyId(companyId);
                    management.setCreatetime(new Date());
                }
                list.add(management);
            }
        }
        materialManagementDao.saveAll(list);
    }

    /**
     * 导出模板
     *
     * @param response
     */
    public void efforts(HttpServletResponse response) {
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("物料管理.xls", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //创建excel文件夹
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("物料管理表");
        // 在sheet里创建第一行，参数为行索引excel的行()
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("物料类型");
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("物料名称");
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellValue("物料代码");
        HSSFCell cell3 = row1.createCell(3);
        cell3.setCellValue("规格");
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellValue("单位");

        String[] strings = new String[]{"原料", "辅料", "成品", "半成品"};

        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
        //设置下拉控制的范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 1024, 0, 0);

//        DVConstraint constraint = DVConstraint.createExplicitListConstraint(strings);
        // 绑定下拉框和作用区域

        DataValidation data_validation = helper.createValidation(constraint, regions);
        sheet.addValidationData(data_validation);
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    public Object selects(String id) {
        return materialManagementDao.findById(id);
    }

    /**
     * 查询所有物理名称
     *
     * @return
     */
    public Object selectAll(String companyId) {
        return materialManagementDao.findByCompanyId(companyId);
    }

    /**
     * 查询成品半成品信息
     *
     * @return
     */
    public Object selectType() {
        String type1 = "成品";
        String type2 = "半成品";
        List<MaterialManagement> byMaterialType = materialManagementDao.findByMaterialType(type1);
        byMaterialType.addAll(materialManagementDao.findByMaterialType(type2));
        return byMaterialType;
    }
}
